﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using ModelLayer;

namespace FremtidensPlejehjem_Server.Database
{
    public static class DbRoom
    {
        #region Storey
        /// <summary>
        /// Creates a Storey, based on the storeyName parameter.
        /// </summary>
        /// <param name="storeyName">Specifies the name to be used.</param>
        public static void CreateStorey(string storeyName)
        {
            string query = "insert into Storey (storeyName) values (@storeyName)";

            using (SqlConnection conn = new SqlConnection(DbGeneral.GetConnectionString()))
            {
                try
                {
                    SqlCommand cmd = new SqlCommand();

                    cmd.CommandText = query;
                    cmd.Connection = conn;
                    cmd.CommandType = CommandType.Text;

                    cmd.Parameters.AddWithValue("@storeyName", storeyName);

                    conn.Open();

                    cmd.ExecuteNonQuery();

                    conn.Close();
                }
                catch (SqlException e)
                {
                    //Insert Logging Here
                    throw;
                }
            }
        }

        /// <summary>
        /// Retrieves a Storey, based on the storeyId parameter.
        /// </summary>
        /// <param name="storeyId">Specifies the ID of the storey to be retrieved.</param>
        public static Storey GetStoreyById(int storeyId)
        {
            string query = "select s.storeyName " +
                           "from Storey s " +
                           "where s.storeyID = @storeyId";

            using (SqlConnection conn = new SqlConnection(DbGeneral.GetConnectionString()))
            {
                try
                {
                    Storey storey = null;

                    SqlCommand cmd = new SqlCommand();

                    cmd.CommandText = query;
                    cmd.Connection = conn;
                    cmd.CommandType = CommandType.Text;

                    cmd.Parameters.AddWithValue("@storeyId", storeyId);

                    conn.Open();

                    SqlDataReader dr = cmd.ExecuteReader();

                    while (dr.Read())
                    {
                        storey = new Storey();

                        storey.Name = dr["storeyName"].ToString();

                        storey.Rooms = GetRoomsByStorey(storeyId);
                    }

                    conn.Close();

                    return storey;
                }
                catch (SqlException e)
                {
                    //Insert Logging Here
                    throw;
                }
            }
        }

        /// <summary>
        /// Retrieves a list of all Storeys.
        /// </summary>
        public static List<Storey> GetAllStoreys()
        {
            string query = "select s.storeyID, s.storeyName " +
                           "from Storey s";

            using (SqlConnection conn = new SqlConnection(DbGeneral.GetConnectionString()))
            {
                try
                {
                    List<Storey> storeys = null;

                    SqlCommand cmd = new SqlCommand();

                    cmd.CommandText = query;
                    cmd.Connection = conn;
                    cmd.CommandType = CommandType.Text;

                    conn.Open();

                    SqlDataReader dr = cmd.ExecuteReader();

                    while (dr.Read())
                    {
                        Storey storey = new Storey();

                        storey.Name = dr["storeyName"].ToString();

                        storey.Rooms = GetRoomsByStorey(Convert.ToInt32(dr["storeyID"]));

                        storeys.Add(storey);
                    }

                    conn.Close();

                    return storeys;
                }
                catch (SqlException e)
                {
                    //Insert Logging Here
                    throw;
                }
            }
        }
        #endregion

        #region Room
        /// <summary>
        /// Creates a room, based on the input parameters.
        /// </summary>
        /// <param name="storeyId">Specifies the ID of the storey, the room is to be added to.</param>
        /// <param name="roomNumber">Specifies the room number to be used.</param>
        /// <param name="roomPurpose">Specifies the purpose of the room.</param>
        public static void CreateRoomOnStorey(int storeyId, int roomNumber, string roomPurpose)
        {
            string query = "insert into Room (roomNumber, purpose) values (@roomNumber, @roomPurpose) " +
                           "insert into Storey_Room (storeyId, roomNumber) values (@storeyId, @roomNumber)";

            using (SqlConnection conn = new SqlConnection(DbGeneral.GetConnectionString()))
            {
                try
                {
                    SqlCommand cmd = new SqlCommand();

                    cmd.CommandText = query;
                    cmd.Connection = conn;
                    cmd.CommandType = CommandType.Text;

                    cmd.Parameters.AddWithValue("@storeyId", storeyId);
                    cmd.Parameters.AddWithValue("@roomNumber", roomNumber);
                    cmd.Parameters.AddWithValue("@roomPurpose", roomPurpose);

                    conn.Open();

                    cmd.ExecuteNonQuery();

                    conn.Close();
                }
                catch (SqlException e)
                {
                    //Insert Logging Here
                    throw;
                }
            }
        }

        /// <summary>
        /// Retrieves a room, based on the roomNumber parameter.
        /// </summary>
        /// <param name="roomNumber">Specifies the room number of the room to be retrieved.</param>
        public static Room GetRoomByNumber(int roomNumber)
        {
            string query = "select r.roomNumber, r.purpose " +
                           "from Room r " +
                           "where r.roomNumber = @roomNumber";

            using (SqlConnection conn = new SqlConnection(DbGeneral.GetConnectionString()))
            {
                try
                {
                    Room room = null;

                    SqlCommand cmd = new SqlCommand();

                    cmd.CommandText = query;
                    cmd.Connection = conn;
                    cmd.CommandType = CommandType.Text;

                    cmd.Parameters.AddWithValue("@roomNumber", roomNumber);

                    conn.Open();

                    SqlDataReader dr = cmd.ExecuteReader();

                    while (dr.Read())
                    {
                        room = new Room();

                        room.RoomNumber = Convert.ToInt32(dr["roomNumber"]);;
                        room.Purpose = dr["purpose"].ToString();
                    }

                    conn.Close();

                    return room;
                }
                catch (SqlException e)
                {
                    //Insert Logging Here
                    throw;
                }
            }
        }

        /// <summary>
        /// Retrieves a room, based on the input parameters.
        /// </summary>
        /// <param name="activityId">Specifies the ID of the activity from the activity room.</param>
        /// <param name="roomNumber">Specifies the number of the room to be retrieved.</param>
        /// <returns>Returns a Room object.</returns>
        public static Room GetRoomByActivityRoom(int activityId, int roomNumber)
        {
            string query = "select r.purpose " +
                           "from Activity_Room ar, Room r " +
                           "where ar.activityID = @activityId " +
                           "and ar.roomNumber = @roomNumber " +
                           "and r.roomNumber = ar.roomNumber";

            using (SqlConnection conn = new SqlConnection(DbGeneral.GetConnectionString()))
            {
                try
                {
                    Room room = null;

                    SqlCommand cmd = new SqlCommand();

                    cmd.CommandText = query;
                    cmd.Connection = conn;
                    cmd.CommandType = CommandType.Text;

                    cmd.Parameters.AddWithValue("@roomNumber", roomNumber);
                    cmd.Parameters.AddWithValue("@activityId", activityId);

                    conn.Open();

                    SqlDataReader dr = cmd.ExecuteReader();

                    while (dr.Read())
                    {
                        room = new Room();

                        room.RoomNumber = roomNumber;
                        room.Purpose = dr["purpose"].ToString();
                    }

                    conn.Close();

                    return room;
                }
                catch (SqlException e)
                {
                    //Insert Logging Here
                    throw;
                }
            }
        }

        /// <summary>
        /// Retrieves a list of rooms, based on the storeyId parameter.
        /// </summary>
        /// <param name="storeyId">Specifies the ID of the storey to get the rooms from.</param>
        public static List<Room> GetRoomsByStorey(int storeyId)
        {
            string query = "select r.roomNumber, r.purpose " +
                           "from Room r, Storey_Room sr " +
                           "where sr.storeyID = @storeyId " +
                           "and r.roomNumber = sr.roomNumber";

            using (SqlConnection conn = new SqlConnection(DbGeneral.GetConnectionString()))
            {
                try
                {
                    List<Room> rooms = new List<Room>();

                    SqlCommand cmd = new SqlCommand();

                    cmd.CommandText = query;
                    cmd.Connection = conn;
                    cmd.CommandType = CommandType.Text;

                    cmd.Parameters.AddWithValue("@storeyId", storeyId);

                    conn.Open();

                    SqlDataReader dr = cmd.ExecuteReader();

                    while (dr.Read())
                    {
                        Room room = new Room();

                        room.RoomNumber = Convert.ToInt32(dr["roomNumber"]);;
                        room.Purpose = dr["purpose"].ToString();

                        rooms.Add(room);
                    }

                    conn.Close();

                    return rooms;
                }
                catch (SqlException e)
                {
                    //Insert Logging Here
                    throw;
                }
            }
        }

        /// <summary>
        /// Retrieves a list of all rooms.
        /// </summary>
        public static List<Room> GetAllRooms()
        {
            string query = "select r.roomNumber, r.purpose " +
                           "from Room r ";

            using (SqlConnection conn = new SqlConnection(DbGeneral.GetConnectionString()))
            {
                try
                {
                    List<Room> rooms = null;

                    SqlCommand cmd = new SqlCommand();

                    cmd.CommandText = query;
                    cmd.Connection = conn;
                    cmd.CommandType = CommandType.Text;

                    conn.Open();

                    SqlDataReader dr = cmd.ExecuteReader();

                    while (dr.Read())
                    {
                        Room room = new Room();

                        room.RoomNumber = Convert.ToInt32(dr["roomNumber"]);;
                        room.Purpose = dr["purpose"].ToString();

                        rooms.Add(room);
                    }

                    conn.Close();

                    return rooms;
                }
                catch (SqlException e)
                {
                    //Insert Logging Here
                    throw;
                }
            }
        }
        #endregion
    }
}
